package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;

import to.Cliente;
import factory.ConnectionFactory;

public class ClienteDAO {
	private final String TABLE = " tb_clientes ";
	private final String COLUM = " id, nome, celular, cep, cidade, cpf, rg, email, endereco, telefone ";
	private final String COLUM_INSERT = " nome, celular, cep, cidade, cpf, rg, email, endereco, telefone ";
	private final String SELECT_FROM = "SELECT "+ COLUM +" FROM "+TABLE;

	public ArrayList<Cliente> execute(String sql){
		ArrayList<Cliente> clientes = new ArrayList<Cliente>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try{
			conn = ConnectionFactory.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();

			while(rs.next()){
				Cliente c = new Cliente();
				
				c.setId(rs.getInt("id"));
				c.setNome(rs.getString("nome"));
				c.setCelular(rs.getString("celular"));
				c.setCep(rs.getString("cep"));
				c.setCidade(rs.getString("cidade"));
				c.setCpf(rs.getString("cpf"));
				c.setRg(rs.getString("rg"));
				c.setEmail(rs.getString("email"));
				c.setEndereco(rs.getString("endereco"));
				c.setTelefone(rs.getString("telefone"));

				clientes.add(c);
			}
		} catch (Exception e) {
			System.out.println("Erro: "+e);
		}finally{
			if(conn != null){
				try { conn.close(); } catch (SQLException e) {}
			}
			if(stmt != null){
				try { stmt.close(); } catch (SQLException e) {}
			}
			if(rs != null){
				try { rs.close(); } catch (SQLException e) {}
			}
		}

		return clientes;
	}

	public List<Cliente> getAll(){

		List<Cliente> clientes = new ArrayList<Cliente>();

		try{
			String sql = SELECT_FROM + " ORDER BY nome";
			clientes = execute(sql);
		} catch (Exception e) {
			System.out.println("Erro: "+e);
		}

		return clientes;
	}

	public Cliente getClientePor(String cpf){
		Cliente cli = null;

		try{
			ArrayList<Cliente> clientes = new ArrayList<Cliente>();
			String sql =  SELECT_FROM + " WHERE CPF = '"+cpf+"'";
			clientes = execute(sql);
			if(!clientes.isEmpty()){
				cli = clientes.get(0);
			}
		} catch (Exception e) {
			System.out.println("Erro: "+e);
		}

		return cli;
	}

	public void inserirCliente(Cliente cli){
		Connection conn = null;
		Statement statement = null;
		try{
			conn = ConnectionFactory.getConnection();

			String sql = "INSERT INTO tb_clientes ( "+ COLUM_INSERT +" ) VALUES ( ";
			sql += " '"+cli.getNome()+"', ";
			sql += " '"+cli.getCelular()+"', ";
			sql += " '"+cli.getCep()+"', ";
			sql += " '"+cli.getCidade()+"', ";
			sql += " '"+cli.getCpf()+"', ";
			sql += " '"+cli.getRg()+"', ";
			sql += " '"+cli.getEmail()+"', ";
			sql += " '"+cli.getEndereco()+"', ";
			sql += " '"+cli.getTelefone()+"' ";
			sql += " ) ";

			statement = (Statement) conn.createStatement();
			statement.executeUpdate(sql);

		}catch(Exception e){
			System.out.println("Erro: "+e);
		}finally{
			if(conn != null){try { conn.close(); } catch (SQLException e) {} }
			if(statement != null){ try { statement.close(); } catch (SQLException e) {}	}
		}
	}

	public void atualizaCliente(Cliente cli) {

		Connection conn = null;
		Statement statement = null;
		try{
			conn = ConnectionFactory.getConnection();

			String sql = "UPDATE tb_clientes SET ";
			sql += " NOME = '"+cli.getNome()+"', ";
			sql += " CELULAR = '"+cli.getCelular()+"', ";
			sql += " CEP = '"+cli.getCep()+"', ";
			sql += " CIDADE = '"+cli.getCidade()+"', ";
			sql += " CPF = '"+cli.getCpf()+"', ";
			sql += " RG = '"+cli.getRg()+"', ";
			sql += " EMAIL = '"+cli.getEmail()+"', ";
			sql += " ENDERECO = '"+cli.getEndereco()+"', ";
			sql += " TELEFONE = '"+cli.getTelefone()+"' ";
			
			sql += " WHERE ID = "+cli.getId();

			statement = (Statement) conn.createStatement();
			statement.executeUpdate(sql);

		}catch(Exception e){
			System.out.println("Erro: "+e);
		}finally{
			if(conn != null){try { conn.close(); } catch (SQLException e) {} }
			if(statement != null){ try { statement.close(); } catch (SQLException e) {}	}
		}
	
	}





}
